package com.feib.soeasy.service.impl;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.annotation.Resource;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.PrintSetup;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;


import com.feib.soeasy.dao.CodeTableDao;
import com.feib.soeasy.dao.CommissionCodeTableDao;
import com.feib.soeasy.dao.GroupDao;
import com.feib.soeasy.dao.PaymentDao;
import com.feib.soeasy.dao.RealAccountDao;
import com.feib.soeasy.dao.Soez5008Dao;
import com.feib.soeasy.model.CommissionCodeTable;
import com.feib.soeasy.model.Group;
import com.feib.soeasy.model.Payment;
import com.feib.soeasy.model.RealAccount;
import com.feib.soeasy.pagination.PaginatedList;
import com.feib.soeasy.service.Soez5008Service;
import com.feib.soeasy.util.CodeTableUtil;
import com.feib.soeasy.util.DateUtil;
import com.feib.soeasy.util.UserUtil;


/**
 * @title (#)Soez5008ServiceImpl.java<br>
 * @description <br>
 * @author Jimmy Liu<br>
 * @version 1.0.0 2010/11/25
 * @copyright Far Eastern International Bank Copyright (c) 2010<br>
 * @2011/3/14 create by Jimmy Liu<br>
 */
@Service("soez5008Service")
public class Soez5008ServiceImpl extends BaseServiceImpl implements Soez5008Service {


	protected Logger logger = LoggerFactory.getLogger(this.getClass());


	@Resource(name="soez5008Dao")
	private Soez5008Dao soez5008Dao;

	@Resource(name="groupDao")
	private GroupDao groupDao;

	@Resource(name="codeTableDao")
	private CodeTableDao codeTableDao;


	@Resource(name="paymentDao")
	private PaymentDao paymentDao;

	@Resource(name="commissionCodeTableDao")
	public CommissionCodeTableDao commissionCodeTableDao;

	@Resource(name="realAccountDao")
	private RealAccountDao realAccountDao;

	/**
	 * 暫存目錄
	 */
	@Resource(name = "tempDirectory")
	private String tempDirectory;




	/**
	 * Create a library of cell styles
	 */
	private static Map<String, CellStyle> createStyles(Workbook wb){
		Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
		CellStyle style;
		Font titleFont = wb.createFont();
		titleFont.setFontName("標楷體");
		titleFont.setFontHeightInPoints((short)24);
		style = wb.createCellStyle();
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		style.setFont(titleFont);
		styles.put("title", style);

		Font title2Font = wb.createFont();
		title2Font.setFontName("標楷體");
		//2011/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題
		title2Font.setFontHeightInPoints((short)12);
		style = wb.createCellStyle();
		style.setFont(title2Font);        
		style.setWrapText(false);//設定文字是否換行
		//2011/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
		style.setAlignment(CellStyle.ALIGN_LEFT);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		styles.put("title2", style);

		Font headerFont = wb.createFont();
		headerFont.setFontName("標楷體");
		headerFont.setFontHeightInPoints((short)12);
		style = wb.createCellStyle();
		style.setFont(headerFont);
		//2011/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
		style.setWrapText(false);//設定文字是否換行
		//2011/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		styles.put("header", style);

		Font cellFont = wb.createFont();
		cellFont.setFontName("標楷體");
		cellFont.setFontHeightInPoints((short)12);
		style = wb.createCellStyle();
		style.setFont(cellFont);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		//2011/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
		style.setWrapText(false);//設定文字是否換行
		//2011/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		styles.put("cell", style);

		style = wb.createCellStyle();
		style.setFont(cellFont);
		style.setAlignment(CellStyle.ALIGN_RIGHT);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		style.setWrapText(false);//設定文字是否換行
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		styles.put("cell_ALIGN_RIGHT", style);

		style = wb.createCellStyle();
		Font cellNumberFont = wb.createFont();
		cellNumberFont.setFontName("Arial");
		cellNumberFont.setFontHeightInPoints((short)14);
		style.setFont(cellNumberFont);
		style.setAlignment(CellStyle.ALIGN_RIGHT);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		//2011/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
		style.setWrapText(false);//設定文字是否換行
		//2011/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		style.setDataFormat(wb.createDataFormat().getFormat("$#,##0"));
		styles.put("cell_number", style);

		style = wb.createCellStyle();
		Font cellDateFont = wb.createFont();
		cellDateFont.setFontName("Arial");
		cellDateFont.setFontHeightInPoints((short)12);
		style.setFont(cellDateFont);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		//2011/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
		style.setWrapText(false);//設定文字是否換行
		//2011/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		style.setDataFormat(wb.createDataFormat().getFormat("yyyy/MM/dd"));
		styles.put("cell_date", style);


		cellFont = wb.createFont();
		cellFont.setFontName("標楷體");
		cellFont.setFontHeightInPoints((short)18);
		cellFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		style = wb.createCellStyle();
		style.setFont(cellFont);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		//2011/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
		style.setWrapText(false);//設定文字是否換行
		//2011/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		styles.put("cell_sub_total", style);

		style = wb.createCellStyle();
		cellNumberFont = wb.createFont();
		cellNumberFont.setFontName("Arial");
		cellNumberFont.setFontHeightInPoints((short)14);
		cellNumberFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		style.setFont(cellNumberFont);
		style.setAlignment(CellStyle.ALIGN_RIGHT);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		//2011/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
		style.setWrapText(false);//設定文字是否換行
		//2011/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		style.setDataFormat(wb.createDataFormat().getFormat("$#,##0"));
		styles.put("cell_number_sub_total", style);




		cellFont = wb.createFont();
		cellFont.setFontName("標楷體");
		cellFont.setFontHeightInPoints((short)14);
		cellFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		style = wb.createCellStyle();
		style.setFont(cellFont);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		//2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
		style.setWrapText(false);//設定文字是否換行
		//2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		styles.put("cell_total", style);

		style = wb.createCellStyle();
		cellNumberFont = wb.createFont();
		cellNumberFont.setFontName("Arial");
		cellNumberFont.setFontHeightInPoints((short)14);
		cellNumberFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		style.setFont(cellNumberFont);
		style.setAlignment(CellStyle.ALIGN_RIGHT);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		//2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
		style.setWrapText(false);//設定文字是否換行
		//2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		style.setDataFormat(wb.createDataFormat().getFormat("$#,##0"));
		styles.put("cell_number_total", style);

		return styles;
	}


	//取得帳單業者 
	private Group getGroupByVirtualAccount(String vaccount)
	{
		String virtual_account_4 = vaccount.substring(0,4); //先比對4碼
		String virtual_account_3 = vaccount.substring(0,3); //4碼找不到再比對3碼
		RealAccount entity = new RealAccount();
		entity.setVirtualAccountNo(virtual_account_4);
		RealAccount account = realAccountDao.queryByVirtualAccount(entity);
		if (account == null) //4碼找不到再比對3碼
		{
			entity.setVirtualAccountNo(virtual_account_3);
			account = realAccountDao.queryByVirtualAccount(entity);
		}
		if (account == null) //3碼4碼皆找不到
			return null;
		else
		{
			return account.getGroup();
		}
	}


	/*取得單種代號明細FeedbackLog  */
	@Override
	public PaginatedList<Map<String, Object>> queryGroupFeedbackLogByChannelCommission(
			Group group, String channel, String commissionCode,
			Date qryDateFrom, Date qryDateTo,PaginatedList<Map<String, Object>> paginationConfig) {
		if (null != qryDateFrom)
		{
			// 將時分秒設定在00:00:00
			qryDateFrom = DateUtil.getBeginOfDateTime(qryDateFrom);
		}

		if (null != qryDateTo)
		{
			// 將時分秒設定在23:59:59
			qryDateTo = DateUtil.getEndOfDateTime(qryDateTo);
		}

		if(null==group)
			group=new Group();

		if (!UserUtil.isBankGroup())
		{
			group=UserUtil.getUserGroup();
		}
		Map<String, String> channelCode=new HashMap<String, String>();

		channelCode.put( "7111111","D");
		channelCode.put("TFM","E" );
		channelCode.put( "HILIFE","F");
		channelCode.put( "OKCVS","G");
		channelCode.put("POST","H");

		PaginatedList<Map<String, Object>> fbLogs = soez5008Dao.queryGroupFeedbackLog(group, channel, commissionCode, qryDateFrom, qryDateTo, paginationConfig);


		if(fbLogs.getPaginatedList()== null || fbLogs.getPaginatedList().size() == 0)
			return null;




		/* 效能提升  超商／郵局傳輸統計查詢  總計記數 */
		Long totalCount = new Long(0);
		Long totalAmount = new Long(0);
		Long totalFee = new Long(0);
		/* 設定複製用PaginatedList 再對資料作處理 不然 foreach的狀況下不能對來源檔案作操作  */
		PaginatedList<Map<String, Object>> results = new PaginatedList<Map<String, Object>>();				
		results.setFullListSize(fbLogs.getFullListSize());
		results.setPageSize(fbLogs.getPageSize());
		results.setPageNumber(fbLogs.getPageNumber());
		results.setSortCriterion(fbLogs.getSortCriterion());
		results.setSortDirection(fbLogs.getSortDirection());

		List<Map<String, Object>> temp = new ArrayList<Map<String, Object>>();
		for(Map<String, Object> fblog:fbLogs.getPaginatedList())
		{	
			//個別Code統計值
			Long codeCount = new Long(0);
			Long codeAmount = new Long(0);
			Long codeFee = new Long(0);
			/* 改善效能 資料改成從db串 */
			/* 畫面上有可能沒傳代碼,所以代碼來源要從資料來  ex:抓整個通路 */		
			commissionCode=(String)fblog.get("code");
			// 取得超商(不含郵局)符合Code手續費
			long fee =((BigDecimal)fblog.get("fee")).longValue();		
			/*超商欄位*/
			String  codeName = CodeTableUtil.getInstance().getCodeName("CHANEL", channel) ;
			fblog.put("codeName", codeName);
			/*代號*/
			fblog.put("commissionCode", commissionCode);	
			fblog.put("gpname", (String)fblog.get("gname"));			
			//計算手續費
			long txamt = ((BigDecimal)fblog.get("txamt")).longValue();
			fblog.put("fee", fee);

			//個別Code統計值
			codeCount++;
			codeAmount += txamt;
			codeFee +=fee;
			//加統計值
			totalCount += codeCount;
			totalAmount += codeAmount;
			totalFee += codeFee;

			fblog.put("totalCount", totalCount);
			fblog.put("totalAmount", totalAmount);
			fblog.put("totalFee", totalFee);

			/*計算總計*/
			temp.add(fblog);

		}

		results.setPaginatedList(temp);
		/* 清除資源*/
		fbLogs.getPaginatedList().clear();
		fbLogs=null;

		return results;
	}

	/** 
	 * 根據通路 代碼 跟日期 查詢顯示畫面資料來源
	 * 取得FeedbackLog 資料 需區分群組
	 * @author jeff 
	 * */
	public List<Map<String, Object>> queryGroupFeedbackLog(Group group,String channel, String code, Date qryDateFrom, Date qryDateTo)
	{
		if (null != qryDateFrom)
		{
			// 將時分秒設定在00:00:00
			qryDateFrom = DateUtil.getBeginOfDateTime(qryDateFrom);
		}

		if (null != qryDateTo)
		{
			// 將時分秒設定在23:59:59
			qryDateTo = DateUtil.getEndOfDateTime(qryDateTo);
		}
		if(null==group)
			group=new Group();

		if (!UserUtil.isBankGroup())
		{
			group=UserUtil.getUserGroup();
		}


		if(channel != null)
		{

			List<Map<String, Object>> fbLogList = soez5008Dao.queryGroupFeedbackLog(group,channel, code, qryDateFrom, qryDateTo);
			if(fbLogList != null && fbLogList.size() != 0)
				return fbLogList;

		}
		return null;
	}
	/*  取得COMMISSCODES 需區分群組*/
	public List<String> queryGroupCommissionCodes(Group group,String channel, Date qryDateFrom, Date qryDateTo)
	{
		if (null != qryDateFrom)
		{
			// 將時分秒設定在00:00:00
			qryDateFrom = DateUtil.getBeginOfDateTime(qryDateFrom);
		}

		if (null != qryDateTo)
		{
			// 將時分秒設定在23:59:59
			qryDateTo = DateUtil.getEndOfDateTime(qryDateTo);
		}

		if(null==group)
			group=new Group();

		if (!UserUtil.isBankGroup())
		{
			group=UserUtil.getUserGroup();
		}

		if(channel != null)
		{
			List<String> commissionCodes = soez5008Dao.queryGroupCommissionCode(group, channel, qryDateFrom, qryDateTo);
			if(commissionCodes != null && commissionCodes.size() != 0)
			{				
				return commissionCodes;
			}
		}
		return null;
	}

	@Override
	/**
	 * 根據通路 代碼 跟日期 取得  顯示畫面 (可分群組)
	 * @author Jeff
	 */
	public String doExportGroupStatisticsHtml(Group group, String[] channels,
			Date[] dateFroms, Date[] dateTos) {
		StringBuffer html = new StringBuffer();

		//總計數據
		Long totalCount = new Long(0);
		Long totalAmount = new Long(0);
		Long totalFee = new Long(0);
		Long totalRealAmount = new Long(0);

		StringBuffer codeHtml = new StringBuffer();

		if(null==group)
			group=new Group();

		if (!UserUtil.isBankGroup())
		{
			group=UserUtil.getUserGroup();
		}
		/* 依通路分類 */
		for(int i=0;i<channels.length;i++)
		{
			String channel = channels[i];
			Date dateFrom = dateFroms[i];
			Date dateTo = dateTos[i];
			StringBuffer codeHtmlSub = new StringBuffer();

			//個別超商統計值
			Long channelCount = new Long(0);
			Long channelAmount = new Long(0);
			Long channelFee = new Long(0);
			Long channelRealAmount = new Long(0);

			if(channel != null && !"".equals(channel))
			{
				//找尋超商的Code,郵局部份會回傳一筆code為null的值
				List<String> codes = this.queryGroupCommissionCodes(group, channel, dateFrom, dateTo);
				if(codes == null)
				{
					SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
					codeHtml.append("<tr>").append("\r\n");
					codeHtml.append("<td align='center'>").append("\r\n");
					codeHtml.append(dateFormat.format(dateFrom)).append("<br>～<br>").append(dateFormat.format(dateTo)).append("\r\n");
					codeHtml.append("</td>").append("\r\n");
					codeHtml.append("<td align='center'>").append("\r\n");
					codeHtml.append(CodeTableUtil.getInstance().getCodeName("CHANEL", channel) ).append("\r\n");
					codeHtml.append("</td>").append("\r\n");
					codeHtml.append("<td colspan='6' align='center'>查無資料").append("\r\n");
					codeHtml.append("</td>").append("\r\n");
					codeHtml.append("<tr>").append("\r\n");
				}else
				{
					int smRowspan = 0;
					int cisCodeRowspan = 0;
					boolean first = true;
					StringBuffer bodyHtml = new StringBuffer();

					for(String code:codes)
					{

						//取得符合channel與commissionCode的每筆Feedback Log資料,組成統計值
						List<Map<String, Object>> fbLogs = this.queryGroupFeedbackLog(group, channel, code, dateFrom, dateTo);

						//個別Code統計值
						Long codeCount = new Long(0);
						Long codeAmount = new Long(0);
						Long codeFee = new Long(0);
						Long codeRealAmount = new Long(0);

						for(Map<String, Object> fblog:fbLogs)
						{
							codeCount++;
							long txamt = ((BigDecimal)fblog.get("txamt")).longValue();  
							codeAmount += txamt;
							/* 改善效能 資料改成從db串 */
							// 取得超商(不含郵局)符合Code手續費
							long fee =((BigDecimal)fblog.get("fee")).longValue();	
							codeFee += fee;
							codeRealAmount += txamt - fee;
						}
						if(fbLogs != null && fbLogs.size() >0)
						{
							smRowspan = codes.size() + 1;
							cisCodeRowspan = 1;

							if(!first)
							{
								bodyHtml.append("<tr>").append("\r\n");
							}
							bodyHtml.append("<td align='center' rowspan='").append(String.valueOf(cisCodeRowspan)).append("'>").append("\r\n");
							if (code ==null) //郵局
							{	
								code = "N/A";
								bodyHtml.append("\r\n");
							}else	
								bodyHtml.append(code).append("\r\n");
							bodyHtml.append("</td>").append("\r\n");

							boolean view = true;

							bodyHtml.append("<td align='right'>").append("\r\n");
							bodyHtml.append(DateUtil.changeToComma(codeCount)).append("\r\n");
							bodyHtml.append("</td>").append("\r\n");
							bodyHtml.append("<td align='right'>").append("\r\n");
							bodyHtml.append(DateUtil.changeToComma(codeAmount)).append("\r\n");
							bodyHtml.append("</td>").append("\r\n");
							bodyHtml.append("<td align='right'>").append("\r\n");
							bodyHtml.append(DateUtil.changeToComma(codeFee)).append("\r\n");
							bodyHtml.append("</td>").append("\r\n");
							bodyHtml.append("<td align='right'>").append("\r\n");
							bodyHtml.append(DateUtil.changeToComma(codeRealAmount)).append("\r\n");
							bodyHtml.append("</td>").append("\r\n");

							if(view)
							{
								//
								SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
								String dateFormS = dateFormat.format(dateFrom);
								String dateFormE = dateFormat.format(dateTo);

								bodyHtml.append("<td align='right' rowspan='").append(String.valueOf(cisCodeRowspan)).append("'>").append("\r\n");
								bodyHtml.append("<div id='viewCommissionCodeImg-").append(channel).append(code).append(dateFormS).append(dateFormE)
								.append("'><a href=\"javascript: void(0)\" class='btnZoom'>查詢</a></div>");
								bodyHtml.append("</td>").append("\r\n");
								view = false;
							}

							bodyHtml.append("</tr>").append("\r\n");
							first = false;


							//加統計值
							channelCount += codeCount;
							channelAmount += codeAmount;
							channelFee += codeFee;
							channelRealAmount += codeRealAmount;

						}
					}

					bodyHtml.append("<tr>").append("\r\n");
					bodyHtml.append("<td align='right' >").append("\r\n");
					bodyHtml.append("統計").append("\r\n");
					bodyHtml.append("</td>").append("\r\n");
					/* 效能提升  超商／郵局傳輸統計查詢  總計記數 */
					bodyHtml.append("<td align='right' >").append("\r\n");
					bodyHtml.append(DateUtil.changeToComma(channelCount)).append("\r\n");
					bodyHtml.append("</td>").append("\r\n");
					bodyHtml.append("<td align='right'>").append("\r\n");
					bodyHtml.append(DateUtil.changeToComma(channelAmount)).append("\r\n");
					bodyHtml.append("</td>").append("\r\n");
					bodyHtml.append("<td align='right'>").append("\r\n");
					bodyHtml.append(DateUtil.changeToComma(channelFee)).append("\r\n");
					bodyHtml.append("</td>").append("\r\n");
					bodyHtml.append("<td align='right'>").append("\r\n");
					bodyHtml.append(DateUtil.changeToComma(channelRealAmount)).append("\r\n");
					bodyHtml.append("</td>").append("\r\n");
					/* 單通路明細 */
					if(channelCount>0){							
						SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
						String dateFormS = dateFormat.format(dateFrom);
						String dateFormE = dateFormat.format(dateTo);						
						bodyHtml.append("<td align='right' >").append("\r\n");
						bodyHtml.append("<div id='viewChannel-")
						.append(channel).append(dateFormS).append(dateFormE)						
						.append("'><a href=\"javascript: void(0)\" class='btnZoom'>查詢</a></div>");
						bodyHtml.append("</td>").append("\r\n");
					}

					bodyHtml.append("</tr>").append("\r\n");

					//=======================================================================
					SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
					codeHtmlSub.append("<tr>").append("\r\n");
					codeHtmlSub.append("<td align='center' rowspan='").append(String.valueOf(smRowspan)).append("'>").append("\r\n");
					codeHtmlSub.append(dateFormat.format(dateFrom)).append("<br>～<br>").append(dateFormat.format(dateTo)).append("\r\n");
					codeHtmlSub.append("</td>").append("\r\n");
					codeHtmlSub.append("<td align='center' rowspan='").append(String.valueOf(smRowspan)).append("'>").append("\r\n");
					codeHtmlSub.append(CodeTableUtil.getInstance().getCodeName("CHANEL", channel) ).append("\r\n");

					codeHtmlSub.append("</td>").append("\r\n");

					codeHtmlSub.append(bodyHtml.toString()).append("\r\n");
					codeHtml.append(codeHtmlSub.toString());


					//總合計
					totalCount += channelCount;
					totalAmount += channelAmount;
					totalFee += channelFee;
					totalRealAmount += channelRealAmount;
				}		
			}
		}

		//------------------------------------------------------------------------------------------------------------


		//串欄位表頭
		String[] titles = {
				"傳輸日期","超商","CODE" ,"代收筆數", "代收金額" , "代收手續費" ,"實撥金額","明細"};

		html.append("<table id=\"listTable\">").append("\r\n");
		html.append("<thead><tr>").append("\r\n");

		for (int j = 0; j < titles.length; j++) {
			html.append("<th>").append(titles[j]).append("</th>").append("\r\n");
		}

		html.append("</tr></thead>").append("\r\n");
		html.append("<tbody>").append("\r\n");


		if(codeHtml.length() != 0)
		{
			html.append(codeHtml.toString()).append("\r\n");

			html.append("<tr>").append("\r\n");
			html.append("<td align='center' colspan='2'>").append("\r\n");
			html.append("總計").append("\r\n");
			html.append("</td>").append("\r\n");	      
			html.append("<td align='right'>").append("\r\n");
			html.append("超商／郵局合計").append("\r\n");
			html.append("</td>").append("\r\n");
			/* 效能提升  超商／郵局傳輸統計查詢  總計記數 */
			html.append("<td align='right'>").append("\r\n");
			html.append(DateUtil.changeToComma(totalCount)).append("\r\n");
			html.append("</td>").append("\r\n");
			html.append("<td align='right'>").append("\r\n");
			html.append(DateUtil.changeToComma(totalAmount)).append("\r\n");
			html.append("</td>").append("\r\n");
			html.append("<td align='right'>").append("\r\n");
			html.append(DateUtil.changeToComma(totalFee)).append("\r\n");
			html.append("</td>").append("\r\n");
			html.append("<td align='right'>").append("\r\n");
			html.append(DateUtil.changeToComma(totalRealAmount)).append("\r\n");
			html.append("</td>").append("\r\n");
			/* 總明細  有總計數目再顯示*/
			if(totalCount>0){	
				html.append("<td align='right' >").append("\r\n");
				html.append("<div id='viewTotal-1")
				.append("'><a href=\"javascript: void(0)\" class='btnZoom'>查詢</a></div>");
				html.append("</td>").append("\r\n");
			}
			html.append("</tr>").append("\r\n");
			// 下載按鈕
			html.append("<tr>").append("\r\n");
			html.append("<td colspan=\"8\" align=\"center\">")
			.append("<input type=\"button\" id=\"doDownloadExcelFileBtn\" value=\"下載(Excel)\" onclick=\"javascript:doDownloadExcelFileAction();\" />")
			.append(" <input type=\"button\" id=\"printBtn2\" value=\"列印\"  onclick=\"javascript:divPrint('queryResultDiv');\" />") 
			.append("</td>").append("\r\n");
			html.append("</tr>").append("\r\n");

			html.append("</tbody></table>").append("\r\n");

			html.append("<script type=\"text/javascript\">").append("\r\n");
			html.append("<!--").append("\r\n");
			html.append("$(document).ready(function() {").append("\r\n");
			html.append("$(\"[id^='viewCommissionCodeImg-']\").click(function() {").append("\r\n");
			html.append("var uid = this.id.substring(22);").append("\r\n");
			html.append("doQueryDetailAction(uid); ").append("\r\n");
			html.append("});").append("\r\n");
			html.append("$(\"[id^='viewTotal-']\").click(function() {").append("\r\n");
			html.append("var uid = this.id.substring(10);").append("\r\n");
			html.append("doQueryTotalAction(uid); ").append("\r\n");
			html.append("});").append("\r\n");
			html.append("$(\"[id^='viewChannel-']\").click(function() {").append("\r\n");
			html.append("var uid = this.id.substring(12);").append("\r\n");
			html.append("doQueryChannelAction(uid); ").append("\r\n");
			html.append("});").append("\r\n");
			html.append("});").append("\r\n");
			html.append("//-->").append("\r\n");
			html.append("</script>").append("\r\n");
			html.append("");	
		}else
		{
			html.append("<tr>").append("\r\n");
			html.append("<td colspan=\"8\" align=\"center\">")
			.append("無超商資料")
			.append("</td>").append("\r\n");
			html.append("</tr>").append("\r\n");
		}


		return html.toString();
	}

	/*新的的取得xls */
	@Override
	public String doExportGroupStatisticsXls(Group group, String[] channels,
			Date[] dateFroms, Date[] dateTos) {

		if(null==group)
			group=new Group();

		if (!UserUtil.isBankGroup())
		{
			group=UserUtil.getUserGroup();
		}

		Workbook wb = new HSSFWorkbook(); // 若要產生xlsx則使用 XSSFWorkbook 即可
		Map<String, CellStyle> styles = createStyles(wb);

		Sheet sheet = wb.createSheet("超商傳輸統計表");
		PrintSetup printSetup = sheet.getPrintSetup();
		printSetup.setLandscape(true);
		sheet.setFitToPage(true);
		sheet.setHorizontallyCenter(true);
		sheet.setZoom(3, 4);
		sheet.setColumnWidth(0, 256*25);
		sheet.setColumnWidth(1, 256*10);
		sheet.setColumnWidth(3, 256*20);
		sheet.setColumnWidth(4, 256*20);
		sheet.setColumnWidth(5, 256*20);
		sheet.setColumnWidth(6, 256*20);


		// 行數
		int rownum = 0;

		//title row
		Row titleRow = sheet.createRow(rownum++);
		titleRow.setHeightInPoints((float)32.25);
		Cell titleCell = titleRow.createCell(0);
		titleCell.setCellValue("超商／郵局傳輸統計表");
		titleCell.setCellStyle(styles.get("title"));
		sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$J$1"));

		Row blankRow = sheet.createRow(rownum++);
		blankRow.setHeightInPoints((float)18);

		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");

		for(int i=0;i<channels.length;i++)
		{
			String channel = channels[i];
			Date dateFrom = dateFroms[i];
			Date dateTo = dateTos[i];
			if(channel != null && !"".equals(channel))
			{
				Row dataRow = sheet.createRow(rownum++);
				Cell dataCell;
				dataCell = dataRow.createCell(0);
				dataCell.setCellValue(CodeTableUtil.getInstance().getCodeName("CHANEL", channel)  + "：" + dateFormat.format(dateFrom)+"～" + dateFormat.format(dateTo));
				dataCell.setCellStyle(styles.get("title2"));
				sheet.addMergedRegion(CellRangeAddress.valueOf("$A$"+rownum+":$J$" + rownum));
			}

		}


		//串欄位表頭
		String[] titles = {
				"傳輸日期","超商","CODE" ,"代收筆數", "代收金額" , "代收手續費", "實撥金額"};

		Row headerRow = sheet.createRow(rownum++);
		headerRow.setHeightInPoints((float) 25.5);
		Cell headerCell;



		for (int i = 0; i < titles.length; i++) {
			headerCell = headerRow.createCell(i);
			headerCell.setCellValue(titles[i]);
			headerCell.setCellStyle(styles.get("header"));
		}

		//總計數據
		Long totalCount = new Long(0);
		Long totalAmount = new Long(0);
		Long totalFee = new Long(0);
		Long totalRealAmount = new Long(0);



		for(int i=0;i<channels.length;i++)
		{
			String channel = channels[i];
			Date dateFrom = dateFroms[i];
			Date dateTo = dateTos[i];
			int start = 0;

			//個別超商統計值
			Long channelCount = new Long(0);
			Long channelAmount = new Long(0);
			Long channelFee = new Long(0);
			Long channelRealAmount = new Long(0);

			List<String> commissionCodes = this.queryGroupCommissionCodes(group, channel, dateFrom, dateTo);
			if(commissionCodes == null)
			{
				if(channel != null && !"".equals(channel))
				{
					Row dataRow = sheet.createRow(rownum++);
					Cell dataCell;
					dataCell = dataRow.createCell(0);
					dataCell.setCellValue(dateFormat.format(dateFrom)+"～" + dateFormat.format(dateTo));
					dataCell.setCellStyle(styles.get("cell"));
					dataCell = dataRow.createCell(1);
					dataCell.setCellValue(CodeTableUtil.getInstance().getCodeName("CHANEL", channel) );
					dataCell.setCellStyle(styles.get("cell"));
					dataCell = dataRow.createCell(2);
					dataCell.setCellValue("查無資料 ");
					dataCell.setCellStyle(styles.get("cell"));
					sheet.addMergedRegion(CellRangeAddress.valueOf("$C$"+rownum+":$J$" + rownum));
				}
			}else
			{
				start = rownum + 1;
				for(String code:commissionCodes)
				{

			
					//取得符合channel與commissionCode的每筆Feedback Log資料,組成統計值
					List<Map<String, Object>> fbLogs = this.queryGroupFeedbackLog(group,channel, code, dateFrom, dateTo);

					//個別Code統計值
					Long codeCount = new Long(0);
					Long codeAmount = new Long(0);
					Long codeFee = new Long(0);
					Long codeRealAmount = new Long(0);

					for(Map<String, Object>fblog:fbLogs)
					{
						codeCount++;
						long txamt = ((BigDecimal)fblog.get("txamt")).longValue();  
						codeAmount += txamt;
						//取得郵局手續費
						long fee =((BigDecimal)fblog.get("fee")).longValue();									
						codeFee += fee;
						codeRealAmount += txamt - fee;
					}

					if(fbLogs != null && fbLogs.size() >0)
					{
						int commissionStart = rownum + 1;

						Row dataRow = sheet.createRow(rownum++);
						Cell dataCell;
						dataCell = dataRow.createCell(0);
						dataCell.setCellValue(dateFormat.format(dateFrom)+"～" + dateFormat.format(dateTo));
						dataCell.setCellStyle(styles.get("cell"));
						dataCell = dataRow.createCell(1);
						dataCell.setCellValue(CodeTableUtil.getInstance().getCodeName("CHANEL", channel) );
						dataCell.setCellStyle(styles.get("cell"));
						dataCell = dataRow.createCell(2);
						dataCell.setCellValue(code);
						dataCell.setCellStyle(styles.get("cell"));
						dataCell = dataRow.createCell(3);
						dataCell.setCellValue(codeCount);
						dataCell.setCellStyle(styles.get("cell_ALIGN_RIGHT"));
						dataCell = dataRow.createCell(4);
						dataCell.setCellValue(codeAmount);
						dataCell.setCellStyle(styles.get("cell_number"));
						dataCell = dataRow.createCell(5);
						dataCell.setCellValue(codeFee);
						dataCell.setCellStyle(styles.get("cell_number"));
						dataCell = dataRow.createCell(6);
						dataCell.setCellValue(codeRealAmount);
						dataCell.setCellStyle(styles.get("cell_number"));

						sheet.addMergedRegion(CellRangeAddress.valueOf("$C$"+commissionStart+":$C$" + rownum));
					}	

					//加統計值
					channelCount += codeCount;
					channelAmount += codeAmount;
					channelFee += codeFee;
					channelRealAmount += codeRealAmount;

				}

				Row dataRow = sheet.createRow(rownum++);
				Cell dataCell;
				dataCell = dataRow.createCell(0);
				dataCell.setCellValue(dateFormat.format(dateFrom)+"～" + dateFormat.format(dateTo));
				dataCell.setCellStyle(styles.get("cell"));
				dataCell = dataRow.createCell(1);
				dataCell.setCellValue(CodeTableUtil.getInstance().getCodeName("CHANEL", channel) );
				dataCell.setCellStyle(styles.get("cell"));

				dataCell = dataRow.createCell(2);				
				dataCell.setCellValue("統計");
				dataCell.setCellStyle(styles.get("cell"));
				//sheet.addMergedRegion(CellRangeAddress.valueOf("$C$"+rownum+":$D$" + rownum));

				/* 效能提升  超商／郵局傳輸統計查詢  總計記數 */
				dataCell = dataRow.createCell(3);
				dataCell.setCellValue(channelCount);
				dataCell.setCellStyle(styles.get("cell_ALIGN_RIGHT"));

				dataCell = dataRow.createCell(4);				
				dataCell.setCellValue(channelAmount);

				dataCell.setCellStyle(styles.get("cell_number"));
				dataCell = dataRow.createCell(5);
				dataCell.setCellValue(channelFee);
				dataCell.setCellStyle(styles.get("cell_number"));
				dataCell = dataRow.createCell(6);
				dataCell.setCellValue(channelRealAmount);
				dataCell.setCellStyle(styles.get("cell_number"));


				//總合計
				totalCount += channelCount;
				totalAmount += channelAmount;
				totalFee += channelFee;
				totalRealAmount += channelRealAmount;

				sheet.addMergedRegion(CellRangeAddress.valueOf("$A$"+start+":$A$" + rownum));
				sheet.addMergedRegion(CellRangeAddress.valueOf("$B$"+start+":$B$" + rownum));
			}

		}

		Row dataRow = sheet.createRow(rownum++);
		Cell dataCell;
		sheet.addMergedRegion(CellRangeAddress.valueOf("$A$"+rownum+":$B$" + rownum));
		dataCell = dataRow.createCell(0);
		dataCell.setCellValue("總計");
		dataCell.setCellStyle(styles.get("cell"));
		dataCell = dataRow.createCell(1);				
		dataCell.setCellStyle(styles.get("cell"));

		//sheet.addMergedRegion(CellRangeAddress.valueOf("$C$"+rownum+":$D$" + rownum));

		dataCell = dataRow.createCell(2);				
		dataCell.setCellValue("超商／郵局合計");
		dataCell.setCellStyle(styles.get("cell"));
		/* 效能提升  超商／郵局傳輸統計查詢  總計記數 */
		dataCell = dataRow.createCell(3);
		dataCell.setCellValue(totalCount);
		dataCell.setCellStyle(styles.get("cell_ALIGN_RIGHT"));
		dataCell = dataRow.createCell(4);
		dataCell.setCellValue(totalAmount);
		dataCell.setCellStyle(styles.get("cell_number"));
		dataCell = dataRow.createCell(5);
		dataCell.setCellValue(totalFee);
		dataCell.setCellStyle(styles.get("cell_number"));
		dataCell = dataRow.createCell(6);
		dataCell.setCellValue(totalRealAmount);
		dataCell.setCellStyle(styles.get("cell_number"));



		int colCount = 0;
		/* 效能提升  超商／郵局傳輸統計查詢  總計記數 */
		while (colCount <= titles.length) {
			sheet.autoSizeColumn((short) colCount++,true);//自動調整欄寬
		}

		String tempDownloadFilePath = tempDirectory + "/" + UserUtil.getUserGroup().getGroupNo() + "/" + UserUtil.getUserId() + "/";
		File directory = new File(tempDownloadFilePath);             
		if(!directory.exists())
		{
			try {
				FileUtils.forceMkdir(directory);
			}
			catch (IOException e) {
				logger.error("{}",e);
			}
		}

		String fileFullName = tempDownloadFilePath + UUID.randomUUID().toString() + "-超商傳輸統計表.xls"; //

		FileOutputStream out;
		try {
			out = new FileOutputStream(fileFullName);
			wb.write(out);
			out.close();
		}
		catch (FileNotFoundException e) {
			logger.error("{}",e);
		}
		catch (IOException e) {
			logger.error("{}",e);
		}
		return fileFullName;
	}

	/**
	 *  取得FeedbackLog (總計明細) */
	public PaginatedList<Map<String, Object>> queryGroupFeedbackLogByChannel(Group group, String channels[],
			Date dateFroms[], Date dateTos[],PaginatedList<Map<String, Object>> paginationConfig) {
		/*
		 *	畫面上的總計放大鏡
		 *	應該要傳所查詢的通路跟各通路的時間
		 */
		Map<String, String> channelCode=new HashMap<String, String>();

		channelCode.put( "7111111","D");
		channelCode.put("TFM","E" );
		channelCode.put( "HILIFE","F");
		channelCode.put( "OKCVS","G");
		channelCode.put("POST","H");



		if(null==group)
			group=new Group();

		if (!UserUtil.isBankGroup())
		{
			group=UserUtil.getUserGroup();
		}

		PaginatedList<Map<String, Object>> fbLogs = soez5008Dao.queryTotalGroupFeedbackLog(group, channels, dateFroms, dateTos, paginationConfig);

		if(fbLogs.getPaginatedList()== null || fbLogs.getPaginatedList().size() == 0)
			return null;

		/* 效能提升  超商／郵局傳輸統計查詢  總計記數 */
		/* 設定複製用PaginatedList 再對資料作處理 不然 foreach的狀況下不能對來源檔案作操作  */
		PaginatedList<Map<String, Object>> results = new PaginatedList<Map<String, Object>>();				
		results.setFullListSize(fbLogs.getFullListSize());
		results.setPageSize(fbLogs.getPageSize());
		results.setPageNumber(fbLogs.getPageNumber());
		results.setSortCriterion(fbLogs.getSortCriterion());
		results.setSortDirection(fbLogs.getSortDirection());

		List<Map<String, Object>> temp = new ArrayList<Map<String, Object>>();
		for(Map<String, Object> fblog:fbLogs.getPaginatedList())
		{	

			String channel = (String)fblog.get("channel");
			String code = (String)fblog.get("code");
			/* 改善效能 資料改成從db串 */
			/* 畫面上有可能沒傳代碼,所以代碼來源要從資料來  ex:抓整個通路 */		
			/*超商欄位*/
			String  codeName = CodeTableUtil.getInstance().getCodeName("CHANEL", channelCode.get(channel)) ;
			fblog.put("codeName", codeName);		
			/*代號*/
			fblog.put("commissionCode", code);
			
			fblog.put("gpname", (String)fblog.get("gname"));
			
			
			/* 總計明細用的總計 顯示在最下方,最後一頁再顯示 */
			if(fbLogs.isLastPage()){	
				Map<String, Object> total=(Map<String, Object>) fbLogs.getTotalObject();
				fblog.put("totalCount", total.get("totalCount"));
				fblog.put("totalAmount", ((BigDecimal)total.get("totalAmount")).longValue());
				fblog.put("totalFee", ((BigDecimal)total.get("totalFee")).longValue());
			}
			/*計算總計*/
			temp.add(fblog);

		}

		results.setPaginatedList(temp);
		/* 清除資源*/
		fbLogs.getPaginatedList().clear();
		fbLogs=null;

		return results;
	}
	@Override
	public String doAjaxQueryGroupCommissionCodeXls(Group group,
			String channel, String commissionCode, Date qryDateFrom,
			Date qryDateTo) {


		List<Map<String, Object>> results = queryGroupFeedbackLogByChannelCommission(group,channel, commissionCode, qryDateFrom, qryDateTo);
		//串欄位表頭
		String[] titles = {
				"傳輸日期","超商","CODE","繳費期限","繳費日期" ,"帳單業者", "繳費帳號", "繳費金額" , "代收手續費","實撥金額"};

		Workbook wb = new HSSFWorkbook(); // 若要產生xlsx則使用 XSSFWorkbook 即可
		Map<String, CellStyle> styles = createStyles(wb);

		Sheet sheet = wb.createSheet("超商傳輸統計明細表");
		PrintSetup printSetup = sheet.getPrintSetup();
		printSetup.setLandscape(true);
		sheet.setFitToPage(true);
		sheet.setHorizontallyCenter(true);
		sheet.setZoom(3, 4);
		sheet.setColumnWidth(0, 256*20);
		sheet.setColumnWidth(1, 256*15);
		sheet.setColumnWidth(2, 256*20);
		sheet.setColumnWidth(3, 256*40);
		sheet.setColumnWidth(4, 256*25);
		sheet.setColumnWidth(5, 256*20);
		sheet.setColumnWidth(6, 256*15);
		sheet.setColumnWidth(7, 256*20);


		// 行數
		int rownum = 0;


		/* 效能提升  超商／郵局傳輸統計查詢  總計記數 */
		Long totalCount = new Long(0);
		Long totalAmount = new Long(0);
		Long totalFee = new Long(0);
		Long totalRealAmount = new Long(0);


		//title row
		Row titleRow = sheet.createRow(rownum++);
		titleRow.setHeightInPoints((float)32.25);
		Cell titleCell = titleRow.createCell(0);
		titleCell.setCellValue("超商／郵局傳輸統計明細表");
		titleCell.setCellStyle(styles.get("title"));
		sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$J$1"));

		Row blankRow = sheet.createRow(rownum++);
		blankRow.setHeightInPoints((float)18);

		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");

		//超商日期資料
		if(channel != null && !"".equals(channel))
		{
			Row dataRow = sheet.createRow(rownum++);
			Cell dataCell;
			dataCell = dataRow.createCell(0);

			String showCode="";
			if(commissionCode != null && !"".equals(commissionCode)){
				showCode= "代碼 : "+commissionCode;
			}

			dataCell.setCellValue(CodeTableUtil.getInstance().getCodeName("CHANEL", channel)  + "：" + dateFormat.format(qryDateFrom)+"～" + dateFormat.format(qryDateTo)+showCode );
			dataCell.setCellStyle(styles.get("title2"));
			sheet.addMergedRegion(CellRangeAddress.valueOf("$A$"+rownum+":$J$" + rownum));
		}

		//欄位表頭
		Row headerRow = sheet.createRow(rownum++);
		headerRow.setHeightInPoints((float) 25.5);
		Cell headerCell;
		for (int i = 0; i < titles.length; i++) {
			headerCell = headerRow.createCell(i);
			headerCell.setCellValue(titles[i]);
			headerCell.setCellStyle(styles.get("header"));
		}


		int no = 1;
		for(Iterator<Map<String, Object>> i=results.iterator();i.hasNext();)
		{
			Map<String, Object> fblog = (Map<String, Object>)i.next();


			/* 效能提升  超商／郵局傳輸統計查詢   總計記數 個別Code統計值 */
			Long codeCount = new Long(0);
			Long codeAmount = new Long(0);
			Long codeFee = new Long(0);
			Long codeRealAmount = new Long(0);

			codeCount++;			
			long txamt = ((BigDecimal)fblog.get("txamt")).longValue();  
			codeAmount += txamt;
			codeFee+= ((Long)fblog.get("fee")).longValue();
			codeRealAmount+=((BigDecimal)fblog.get("txamt")).longValue()-((Long)fblog.get("fee")).longValue();
			Row dataRow = sheet.createRow(rownum++);
			Cell dataCell;
			dataCell = dataRow.createCell(0);
			String fbDate = (String)fblog.get("fbdate");
			dataCell.setCellValue(fbDate.substring(0,4)+"/"+fbDate.substring(4,6)+"/"+fbDate.substring(6,8));
			dataCell.setCellStyle(styles.get("cell_date"));

			/* 效能提升  超商／郵局傳輸統計查詢  總計記數 */
			dataCell = dataRow.createCell(1);
			dataCell.setCellValue(CodeTableUtil.getInstance().getCodeName("CHANEL", channel) );
			dataCell.setCellStyle(styles.get("cell"));
			dataCell = dataRow.createCell(2);
			dataCell.setCellValue((String)fblog.get("code"));
			dataCell.setCellStyle(styles.get("cell"));

			dataCell = dataRow.createCell(3);



			Date dueday = (Date)fblog.get("dueday");
			if (dueday==null)
				dataCell.setCellValue("N/A");
			else
				dataCell.setCellValue(dateFormat.format(dueday));


			dataCell.setCellStyle(styles.get("cell_date"));
			dataCell = dataRow.createCell(4);
			String aday = (String)fblog.get("aday");
			dataCell.setCellValue(aday.substring(0,4)+"/"+aday.substring(4,6)+"/"+aday.substring(6,8));
			dataCell.setCellStyle(styles.get("cell_date"));
			dataCell = dataRow.createCell(5);
			dataCell.setCellValue((String)fblog.get("gname"));
			dataCell.setCellStyle(styles.get("cell"));
			dataCell = dataRow.createCell(6);
			dataCell.setCellValue((String)fblog.get("userno"));
			dataCell.setCellStyle(styles.get("cell"));
			dataCell = dataRow.createCell(7);
			dataCell.setCellValue(((BigDecimal)fblog.get("txamt")).longValue());
			dataCell.setCellStyle(styles.get("cell_number"));
			dataCell = dataRow.createCell(8);
			dataCell.setCellValue(((Long)fblog.get("fee")).longValue());
			dataCell.setCellStyle(styles.get("cell_number"));
			dataCell = dataRow.createCell(9);
			dataCell.setCellValue(((BigDecimal)fblog.get("txamt")).longValue()-((Long)fblog.get("fee")).longValue());
			dataCell.setCellStyle(styles.get("cell_number"));
			no++; 
			/* 效能提升  超商／郵局傳輸統計查詢  總計記數 */
			totalCount += codeCount;
			totalAmount += codeAmount;
			totalFee += codeFee;
			totalRealAmount += codeRealAmount;

		}

		Row dataRow = sheet.createRow(rownum++);
		Cell dataCell;

		dataCell = dataRow.createCell(0);
		dataCell.setCellStyle(styles.get("cell"));
		dataCell = dataRow.createCell(1);				
		dataCell.setCellStyle(styles.get("cell"));	
		dataCell = dataRow.createCell(2);						
		dataCell.setCellStyle(styles.get("cell"));
		dataCell = dataRow.createCell(3);						
		dataCell.setCellStyle(styles.get("cell"));
		dataCell = dataRow.createCell(4);						
		dataCell.setCellStyle(styles.get("cell"));
		/* 效能提升  超商／郵局傳輸統計查詢  總計記數 */
		dataCell = dataRow.createCell(5);						
		dataCell.setCellStyle(styles.get("cell"));
		dataCell.setCellValue("總計");
		dataCell = dataRow.createCell(6);
		dataCell.setCellValue(totalCount+"筆");
		dataCell.setCellStyle(styles.get("cell_ALIGN_RIGHT"));
		dataCell = dataRow.createCell(7);
		dataCell.setCellValue(totalAmount);
		dataCell.setCellStyle(styles.get("cell_number"));
		dataCell = dataRow.createCell(8);
		dataCell.setCellValue(totalFee);
		dataCell.setCellStyle(styles.get("cell_number"));
		dataCell = dataRow.createCell(9);
		dataCell.setCellValue(totalRealAmount);
		dataCell.setCellStyle(styles.get("cell_number"));



		int colCount = 0;
		/* 效能提升  超商／郵局傳輸統計查詢  總計記數 */
		while (colCount <= titles.length) {
			sheet.autoSizeColumn((short) colCount++,true);//自動調整欄寬
		}


		String tempDownloadFilePath = tempDirectory + "/" + UserUtil.getUserGroup().getGroupNo() + "/" + UserUtil.getUserId() + "/";
		File directory = new File(tempDownloadFilePath);             
		if(!directory.exists())
		{
			try {
				FileUtils.forceMkdir(directory);
			}
			catch (IOException e) {
				logger.error("{}",e);
			}
		}

		String fileFullName = tempDownloadFilePath + UUID.randomUUID().toString() + "-超商傳輸統計明細表.xls";

		FileOutputStream out;
		try {
			out = new FileOutputStream(fileFullName);
			wb.write(out);
			out.close();
		}
		catch (FileNotFoundException e) {
			logger.error("{}",e);
		}
		catch (IOException e) {
			logger.error("{}",e);
		}
		return fileFullName;
	}
	@Override
	public List<Map<String, Object>> queryGroupFeedbackLogByChannelCommission(
			Group group, String channel, String commissionCode,
			Date qryDateFrom, Date qryDateTo) {
		if (null != qryDateFrom)
		{
			// 將時分秒設定在00:00:00
			qryDateFrom = DateUtil.getBeginOfDateTime(qryDateFrom);
		}

		if (null != qryDateTo)
		{
			// 將時分秒設定在23:59:59
			qryDateTo = DateUtil.getEndOfDateTime(qryDateTo);
		}

		List<Map<String, Object>> fbLogs = soez5008Dao.queryGroupFeedbackLog(group,channel, commissionCode, qryDateFrom, qryDateTo);

		if(fbLogs == null || fbLogs.size() == 0)
			return null;




		/* 效能提升  超商／郵局傳輸統計查詢  總計記數 */
		Long totalCount = new Long(0);
		Long totalAmount = new Long(0);
		Long totalFee = new Long(0);

		List<Map<String, Object>> results = new ArrayList<Map<String, Object>>();
		for(Iterator<Map<String, Object>> iter=fbLogs.iterator();iter.hasNext();)
		{
			Map<String, Object> fblog = (Map<String, Object>)iter.next();

			//個別Code統計值
			Long codeCount = new Long(0);
			Long codeAmount = new Long(0);
			Long codeFee = new Long(0);

			commissionCode=(String)fblog.get("code");

		

			/*超商欄位*/
			String  codeName =  CodeTableUtil.getInstance().getCodeName("CHANEL", channel) ;
			fblog.put("codeName", codeName);
			/*代號*/
			fblog.put("commissionCode", (String)fblog.get("code"));
			/* 改善效能 資料改成從db串 */
			/* 畫面上有可能沒傳代碼,所以代碼來源要從資料來  ex:抓整個通路 */		
			commissionCode=(String)fblog.get("code");
			// 取得超商(不含郵局)符合Code手續費
			long fee =((BigDecimal)fblog.get("fee")).longValue();		
				//計算手續費
			long txamt = ((BigDecimal)fblog.get("txamt")).longValue();
			fblog.put("fee", fee);

			//個別Code統計值
			codeCount++;
			codeAmount += txamt;
			codeFee +=fee;
			//加統計值
			totalCount += codeCount;
			totalAmount += codeAmount;
			totalFee += codeFee;

			fblog.put("totalCount", totalCount);
			fblog.put("totalAmount", totalAmount);
			fblog.put("totalFee", totalFee);

			results.add(fblog);


		}




		return results;
	}
	@Override
	public String doExportGroupDetailStatisticsXls(Group group,
			String[] channels, Date[] dateFroms, Date[] dateTos) {
		if(null==group)
			group=new Group();

		if (!UserUtil.isBankGroup())
		{
			group=UserUtil.getUserGroup();
		}

		Workbook wb = new HSSFWorkbook(); // 若要產生xlsx則使用 XSSFWorkbook 即可
		Map<String, CellStyle> styles = createStyles(wb);

		Sheet sheet = wb.createSheet("超商傳輸統計表");
		PrintSetup printSetup = sheet.getPrintSetup();
		printSetup.setLandscape(true);
		sheet.setFitToPage(true);
		sheet.setHorizontallyCenter(true);
		sheet.setZoom(3, 4);
		sheet.setColumnWidth(0, 256*25);
		sheet.setColumnWidth(1, 256*10);
		sheet.setColumnWidth(3, 256*20);
		sheet.setColumnWidth(4, 256*20);
		sheet.setColumnWidth(5, 256*20);
		sheet.setColumnWidth(6, 256*20);


		// 行數
		int rownum = 0;

		//title row
		Row titleRow = sheet.createRow(rownum++);
		titleRow.setHeightInPoints((float)32.25);
		Cell titleCell = titleRow.createCell(0);
		titleCell.setCellValue("超商／郵局傳輸統計表");
		titleCell.setCellStyle(styles.get("title"));
		sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$J$1"));

		Row blankRow = sheet.createRow(rownum++);
		blankRow.setHeightInPoints((float)18);

		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");

		for(int i=0;i<channels.length;i++)
		{
			String channel = channels[i];
			Date dateFrom = dateFroms[i];
			Date dateTo = dateTos[i];
			if(channel != null && !"".equals(channel))
			{
				Row dataRow = sheet.createRow(rownum++);
				Cell dataCell;
				dataCell = dataRow.createCell(0);
				dataCell.setCellValue(CodeTableUtil.getInstance().getCodeName("CHANEL", channel)  + "：" + dateFormat.format(dateFrom)+"～" + dateFormat.format(dateTo));
				dataCell.setCellStyle(styles.get("title2"));
				sheet.addMergedRegion(CellRangeAddress.valueOf("$A$"+rownum+":$J$" + rownum));
			}

		}


		//串欄位表頭
		String[] titles = {
				"傳輸日期","超商","CODE","繳費期限","繳費日期" ,"帳單業者", "繳費帳號", "繳費金額" , "代收手續費","實撥金額"};

		Row headerRow = sheet.createRow(rownum++);
		headerRow.setHeightInPoints((float) 25.5);
		Cell headerCell;



		for (int i = 0; i < titles.length; i++) {
			headerCell = headerRow.createCell(i);
			headerCell.setCellValue(titles[i]);
			headerCell.setCellStyle(styles.get("header"));
		}

		//總計數據
		Long totalCount = new Long(0);
		Long totalAmount = new Long(0);
		Long totalFee = new Long(0);
		Long totalRealAmount = new Long(0);



		for(int i=0;i<channels.length;i++)
		{
			String channel = channels[i];
			Date dateFrom = dateFroms[i];
			Date dateTo = dateTos[i];
			int start = 0;

			//個別超商統計值
			Long channelCount = new Long(0);
			Long channelAmount = new Long(0);
			Long channelFee = new Long(0);
			Long channelRealAmount = new Long(0);

			List<String> commissionCodes = this.queryGroupCommissionCodes(group, channel, dateFrom, dateTo);
			if(commissionCodes == null)
			{
				if(channel != null && !"".equals(channel))
				{
					Row dataRow = sheet.createRow(rownum++);
					Cell dataCell;
					dataCell = dataRow.createCell(0);
					dataCell.setCellValue(dateFormat.format(dateFrom)+"～" + dateFormat.format(dateTo));
					dataCell.setCellStyle(styles.get("cell"));
					dataCell = dataRow.createCell(1);
					dataCell.setCellValue(CodeTableUtil.getInstance().getCodeName("CHANEL", channel) );
					dataCell.setCellStyle(styles.get("cell"));
					dataCell = dataRow.createCell(2);
					dataCell.setCellValue("查無資料 ");
					dataCell.setCellStyle(styles.get("cell"));
					sheet.addMergedRegion(CellRangeAddress.valueOf("$C$"+rownum+":$J$" + rownum));
				}
			}else
			{
				start = rownum + 1;
				for(String code:commissionCodes)
				{

					
			
					//取得符合channel與commissionCode的每筆Feedback Log資料,組成統計值
					List<Map<String, Object>> fbLogs = this.queryGroupFeedbackLog(group,channel, code, dateFrom, dateTo);

					//個別Code統計值
					Long codeCount = new Long(0);
					Long codeAmount = new Long(0);
					Long codeFee = new Long(0);
					Long codeRealAmount = new Long(0);

					for(Map<String, Object>fblog:fbLogs)
					{
						codeCount++;
						long txamt = ((BigDecimal)fblog.get("txamt")).longValue();  
						codeAmount += txamt;

						String gpname=(String)fblog.get("gname");
						long fee =((BigDecimal)fblog.get("fee")).longValue();					
						codeFee += fee;
						codeRealAmount += txamt - fee;

						Row dataRow = sheet.createRow(rownum++);
						Cell dataCell;
						dataCell = dataRow.createCell(0);
						String fbDate = (String)fblog.get("fbdate");
						dataCell.setCellValue(fbDate.substring(0,4)+"/"+fbDate.substring(4,6)+"/"+fbDate.substring(6,8));
						dataCell.setCellStyle(styles.get("cell_date"));

						/* 效能提升  超商／郵局傳輸統計查詢  總計記數 */
						dataCell = dataRow.createCell(1);
						dataCell.setCellValue(CodeTableUtil.getInstance().getCodeName("CHANEL", channel) );
						dataCell.setCellStyle(styles.get("cell"));
						dataCell = dataRow.createCell(2);
						dataCell.setCellValue((String)fblog.get("code"));
						dataCell.setCellStyle(styles.get("cell"));

						dataCell = dataRow.createCell(3);

							Date dueday = (Date)fblog.get("dueday");
							if (dueday==null)
								dataCell.setCellValue("N/A");
							else
								dataCell.setCellValue(dateFormat.format(dueday));

						dataCell.setCellStyle(styles.get("cell_date"));
						dataCell = dataRow.createCell(4);
						String aday = (String)fblog.get("aday");
						dataCell.setCellValue(aday.substring(0,4)+"/"+aday.substring(4,6)+"/"+aday.substring(6,8));
						dataCell.setCellStyle(styles.get("cell_date"));
						dataCell = dataRow.createCell(5);
						dataCell.setCellValue(gpname);
						dataCell.setCellStyle(styles.get("cell"));
						dataCell = dataRow.createCell(6);
						dataCell.setCellValue((String)fblog.get("userno"));
						dataCell.setCellStyle(styles.get("cell"));
						dataCell = dataRow.createCell(7);
						dataCell.setCellValue(txamt);
						dataCell.setCellStyle(styles.get("cell_number"));
						dataCell = dataRow.createCell(8);
						dataCell.setCellValue(fee);
						dataCell.setCellStyle(styles.get("cell_number"));
						dataCell = dataRow.createCell(9);
						dataCell.setCellValue(txamt-fee);
						dataCell.setCellStyle(styles.get("cell_number"));


					}
					//加統計值
					channelCount += codeCount;
					channelAmount += codeAmount;
					channelFee += codeFee;
					channelRealAmount += codeRealAmount;

				}

				Row dataRow = sheet.createRow(rownum++);
				Cell dataCell;
				dataCell = dataRow.createCell(0);
				dataCell.setCellValue(dateFormat.format(dateFrom)+"～" + dateFormat.format(dateTo));
				dataCell.setCellStyle(styles.get("cell"));
				dataCell = dataRow.createCell(1);
				dataCell.setCellValue(CodeTableUtil.getInstance().getCodeName("CHANEL", channel) );
				dataCell.setCellStyle(styles.get("cell"));

				dataCell = dataRow.createCell(2);
				dataCell.setCellValue("統計");
				dataCell.setCellStyle(styles.get("cell"));
				dataCell = dataRow.createCell(3);
				dataCell.setCellStyle(styles.get("cell"));
				dataCell = dataRow.createCell(4);
				dataCell.setCellStyle(styles.get("cell"));
				dataCell = dataRow.createCell(5);				

				dataCell.setCellStyle(styles.get("cell"));
				sheet.addMergedRegion(CellRangeAddress.valueOf("$C$"+rownum+":$f$" + rownum));

				/* 效能提升  超商／郵局傳輸統計查詢  總計記數 */
				dataCell = dataRow.createCell(6);
				dataCell.setCellValue(channelCount);
				dataCell.setCellStyle(styles.get("cell_ALIGN_RIGHT"));

				dataCell = dataRow.createCell(7);				
				dataCell.setCellValue(channelAmount);

				dataCell.setCellStyle(styles.get("cell_number"));
				dataCell = dataRow.createCell(8);
				dataCell.setCellValue(channelFee);
				dataCell.setCellStyle(styles.get("cell_number"));
				dataCell = dataRow.createCell(9);
				dataCell.setCellValue(channelRealAmount);
				dataCell.setCellStyle(styles.get("cell_number"));


				//總合計
				totalCount += channelCount;
				totalAmount += channelAmount;
				totalFee += channelFee;
				totalRealAmount += channelRealAmount;

				sheet.addMergedRegion(CellRangeAddress.valueOf("$A$"+start+":$A$" + rownum));
				sheet.addMergedRegion(CellRangeAddress.valueOf("$B$"+start+":$B$" + rownum));
			}

		}

		Row dataRow = sheet.createRow(rownum++);
		Cell dataCell;
		sheet.addMergedRegion(CellRangeAddress.valueOf("$A$"+rownum+":$B$" + rownum));
		dataCell = dataRow.createCell(0);
		dataCell.setCellValue("總計");
		dataCell.setCellStyle(styles.get("cell"));
		dataCell = dataRow.createCell(1);				
		dataCell.setCellStyle(styles.get("cell"));

		sheet.addMergedRegion(CellRangeAddress.valueOf("$C$"+rownum+":$F$" + rownum));

		dataCell = dataRow.createCell(2);
		dataCell.setCellValue("超商／郵局合計");
		dataCell.setCellStyle(styles.get("cell"));
		dataCell = dataRow.createCell(3);
		dataCell.setCellStyle(styles.get("cell"));
		dataCell = dataRow.createCell(4);
		dataCell.setCellStyle(styles.get("cell"));
		dataCell = dataRow.createCell(5);					
		dataCell.setCellStyle(styles.get("cell"));
		/* 效能提升  超商／郵局傳輸統計查詢  總計記數 */
		dataCell = dataRow.createCell(6);
		dataCell.setCellValue(totalCount);
		dataCell.setCellStyle(styles.get("cell_ALIGN_RIGHT"));
		dataCell = dataRow.createCell(7);
		dataCell.setCellValue(totalAmount);
		dataCell.setCellStyle(styles.get("cell_number"));
		dataCell = dataRow.createCell(8);
		dataCell.setCellValue(totalFee);
		dataCell.setCellStyle(styles.get("cell_number"));
		dataCell = dataRow.createCell(9);
		dataCell.setCellValue(totalRealAmount);
		dataCell.setCellStyle(styles.get("cell_number"));



		int colCount = 0;
		/* 效能提升  超商／郵局傳輸統計查詢  總計記數 */
		while (colCount <= titles.length) {
			sheet.autoSizeColumn((short) colCount++,true);//自動調整欄寬
		}

		String tempDownloadFilePath = tempDirectory + "/" + UserUtil.getUserGroup().getGroupNo() + "/" + UserUtil.getUserId() + "/";
		File directory = new File(tempDownloadFilePath);             
		if(!directory.exists())
		{
			try {
				FileUtils.forceMkdir(directory);
			}
			catch (IOException e) {
				logger.error("{}",e);
			}
		}

		String fileFullName = tempDownloadFilePath + UUID.randomUUID().toString() + "-超商傳輸統計表.xls"; //

		FileOutputStream out;
		try {
			out = new FileOutputStream(fileFullName);
			wb.write(out);
			out.close();
		}
		catch (FileNotFoundException e) {
			logger.error("{}",e);
		}
		catch (IOException e) {
			logger.error("{}",e);
		}
		return fileFullName;
	}
}
